package com.ruoyi.standingbook.sorting.service.impl;

import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.ruoyi.common.core.service.OssService;
import com.ruoyi.common.domain.vo.SortingErecordVo;
import com.ruoyi.common.domain.vo.StatisticsSortingVo;
import com.ruoyi.common.domain.vo.StatisticsToiletVo;
import com.ruoyi.common.domain.vo.ToiletErecordVo;
import com.ruoyi.common.tools.constant.ERecordConstant;
import com.ruoyi.common.tools.excel.MyExcelUtil;
import com.ruoyi.common.tools.excel.base.MyExcelResult;
import com.ruoyi.common.tools.excel.listener.SortingExcelListener;
import com.ruoyi.common.tools.utils.SerialGenerator;
import com.ruoyi.standingbook.sorting.domain.bo.SortingBo;
import com.ruoyi.standingbook.sorting.domain.bo.SortingImportBaseBo;
import com.ruoyi.standingbook.sorting.domain.vo.SortingImportDetailVo;
import com.ruoyi.standingbook.sorting.domain.vo.SortingVo;
import com.ruoyi.standingbook.sorting.service.ISortingImportBaseService;
import com.ruoyi.standingbook.sorting.service.ISortingImportDetailService;
import com.ruoyi.standingbook.sorting.service.ISortingService;
import com.ruoyi.standingbook.toilet.domain.bo.ToiletBo;
import com.ruoyi.standingbook.toilet.domain.vo.ToiletVo;
import lombok.RequiredArgsConstructor;
import org.apache.http.client.utils.DateUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.*;

@RequiredArgsConstructor
@Service
public class SortingExcelService {
    private final ISortingService sortingService;
    private final ISortingImportBaseService sortingImportBaseService;
    private final ISortingImportDetailService sortingImportDetailService;
    private final OssService ossService;

    /**
     * 导入excel数据
     *
     * @param bo
     * @return
     */
    public Boolean importExcel(SortingImportBaseBo bo) {
        // 生成批次号
        final String batchNo = SerialGenerator.generator.nextId();
        bo.setBatchNo(batchNo);

        // excel 处理
        MyExcelResult<SortingImportDetailVo> excelResult = MyExcelUtil.getImportExcelData(batchNo, bo.getExcelUrl(), new SortingExcelListener(batchNo), SortingImportDetailVo.class, true);

        if (null == excelResult) {
            return false;
        }

        // 更新主表状态
        if (excelResult.isSuccess()) {
            bo.setProcessStatus(ERecordConstant.COMMON_STATUS_SUCCESS);
        } else {
            bo.setProcessStatus(ERecordConstant.COMMON_STATUS_ERROR);
        }
        bo.setProcessMsg(excelResult.getAnalysis());
        bo.setAttachMsg(ERecordConstant.ATTACH_UPLOAD_STATUS_NO);
        // 入库主表
        if (this.sortingImportBaseService.insertByBo(bo)) {
            // 入库子表
            if (!excelResult.getList().isEmpty()) {
                if (!this.sortingImportDetailService.batchInsertVo(excelResult.getList())) {
                    return false;
                }
            }
        } else {
            return false;
        }

        if (excelResult.isSuccess() && !excelResult.getList().isEmpty()) {
            this.sortingService.batchInsert(excelResult.getList());
        }
        return true;
    }

    /**
     * 导出市政垃圾分类督导日报
     */
    public void exportDailyReport(SortingBo bo, HttpServletResponse response) {
        String[] communitys = {"田寮社区", "玉律社区", "长圳社区", "红星社区"};
        try {
            OutputStream output = response.getOutputStream();
            // 产生工作薄对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            //查询当月垃圾分类督导汇总数据
            bo.setDate(DateUtils.formatDate(new Date(), "yyyy-MM"));
            SortingErecordVo sev = sortingService.getSortingErecordInfo(bo);
            for (int c = 0; c < communitys.length; c++) {
                Map<String, Boolean> coordinateMap = new HashMap<>(); //坐标map
                bo.setCommunity(communitys[c]);
                List<SortingVo> svList = sortingService.queryExcelList(bo);
                // 产生工作表对象
                HSSFSheet sheet = workbook.createSheet();
                // 设置工作表的名称.
                workbook.setSheetName(c, communitys[c]);
                HSSFRow row;
                HSSFCell cell;// 产生单元格
                int rowIndex = 0;
                row = sheet.createRow(rowIndex);// 产生一行
                row.setHeight((short) 700);
                /* *********普通列样式********* */
                HSSFFont font = workbook.createFont();
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                font.setFontName("Arail narrow"); // 字体
                //标题列
                String[] titles = {"", "", "", "序号", "日期", "督导社区", "督导收集点名称", "督导投放点名称", "督导人员", "手机号码", "督导要求", "督导时长（小时）"};
                int[] widths = {625 * 14, 625 * 15, 625 * 5, 625 * 5, 625 * 6, 625 * 6, 625 * 6, 625 * 6, 625 * 8, 625 * 8, 625 * 15, 625 * 8};
                for (int i = 0; i < titles.length; i++) {
                    // 创建列
                    cell = row.createCell(i);
                    // 字体颜色
                    font.setColor(HSSFFont.COLOR_NORMAL);
                    cellStyle.setFont(font);
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                    //设置上边框线条类型
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    //设置右边框线条类型
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    //设置下边框线条类型
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    //设置左边框线条类型
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    //设置上边框线条颜色
                    cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                    //设置右边框线条颜色
                    cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                    //设置下边框线条颜色
                    cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                    //设置左边框线条颜色
                    cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                    cell.setCellStyle(cellStyle);
                    sheet.setColumnWidth(i, widths[i]);
                    // 设置列中写入内容为String类型
                    cell.setCellType(CellType.STRING);
                    // 写入列名
                    cell.setCellValue(titles[i]);
                }
                /* *************创建内容列*************** */
                font = workbook.createFont();
                //cellStyle = workbook.createCellStyle(); //继续沿用上面的样式
                String pic1Url = "";
                List<String> picList1 = new ArrayList<>();
                List<String> picList2 = new ArrayList<>();
                List<String> picList3 = new ArrayList<>();
                int rowSize = 21;
                if (svList.size() + 1 > 21) {
                    rowSize = svList.size() + 1;
                }
                for (int r = 1; r < rowSize; r++) {
                    row = sheet.createRow(r);// 产生一行
                    row.setHeight((short) 700);
                    String date = "";
                    String community = "";
                    String collectionName = "";
                    String deliveryName = "";
                    String superintendentUserName = "";
                    String superintendentUserPhone = "";
                    String supervisionAsk = "";
                    String actualDuration = "";
                    if (svList.size() > 0) {
                        if (svList.size() >= r) {
                            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                            date = format.format(svList.get(r - 1).getDate());
                            community = svList.get(r - 1).getCommunity();
                            collectionName = svList.get(r - 1).getCollectionName();
                            deliveryName = svList.get(r - 1).getDeliveryName();
                            superintendentUserName = svList.get(r - 1).getSuperintendentUserName();
                            superintendentUserPhone = svList.get(r - 1).getSuperintendentUserPhone();
                            supervisionAsk = svList.get(r - 1).getSupervisionAsk();
                            actualDuration = svList.get(r - 1).getActualDuration() == null ? "" : svList.get(r - 1).getActualDuration().toString();

                            pic1Url = svList.get(r - 1).getPic1() != null ? ossService.selectUrlByIds(svList.get(r - 1).getPic1()) : "";
                            String pic2Url = svList.get(r - 1).getPic2() != null ? ossService.selectUrlByIds(svList.get(r - 1).getPic2()) : "";
                            if(!pic1Url.equals("")) {
                                picList1.add(pic1Url);
                            }
                            //picList1.add(pic2Url);
                            String pic3Url = svList.get(r - 1).getPic3() != null ? ossService.selectUrlByIds(svList.get(r - 1).getPic3()) : "";
                            String pic4Url = svList.get(r - 1).getPic4() != null ? ossService.selectUrlByIds(svList.get(r - 1).getPic4()) : "";
                            if(!pic3Url.equals("")) {
                                picList2.add(pic3Url);
                            }
                            //picList2.add(pic4Url);
                            String pic5Url = svList.get(r - 1).getPic5() != null ? ossService.selectUrlByIds(svList.get(r - 1).getPic5()) : "";
                            String pic6Url = svList.get(r - 1).getPic6() != null ? ossService.selectUrlByIds(svList.get(r - 1).getPic6()) : "";
                            if(!pic5Url.equals("")) {
                                picList3.add(pic5Url);
                            }
                            // picList3.add(pic6Url);
                        }
                    }
                    for (int i = 0; i < 12; i++) {
                        // 创建列
                        cell = row.createCell(i);
                        // 字体颜色
                        font.setColor(HSSFFont.COLOR_NORMAL);
                        cellStyle.setFont(font);
                        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                        cell.setCellStyle(cellStyle);
                        // 设置列中写入内容为String类型
                        cell.setCellType(CellType.STRING);
                        // 写入列名
                        List<StatisticsSortingVo> ssvList = sev.getSortingNumList();
                        if (r < (4 * communitys.length) + 1) {
                            if (i == 0) {
                                for (int y = 0; y < communitys.length; y++) {
                                    StatisticsSortingVo ssv = new StatisticsSortingVo();
                                    for (StatisticsSortingVo statisticsSortingVo : ssvList) {
                                        if (statisticsSortingVo.getCommunity().equals(communitys[y])) {
                                            ssv = statisticsSortingVo;
                                            break;
                                        }
                                    }
                                    List<StatisticsSortingVo> todayList = sev.getSortingTodayList();
                                    StatisticsSortingVo todaySsv = new StatisticsSortingVo();
                                    for (StatisticsSortingVo statisticsSortingVo : todayList) {
                                        if (statisticsSortingVo.getCommunity().equals(communitys[y])) {
                                            todaySsv = statisticsSortingVo;
                                            break;
                                        }
                                    }
                                    String title = "";
                                    if (r == 1 || r == (1 + (y * 4))) {
                                        title = communitys[y];
                                        cell.setCellValue(title);
                                        sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 1));
                                        break;
                                    } else if (r == 2 || r == (2 + (y * 4))) {
                                        title = "督导投放点数量：" + (ssv.getDeliveryNum() == null ? 0 : ssv.getDeliveryNum());
                                        cell.setCellValue(title);
                                        break;
                                    } else if (r == 3 || r == (3 + (y * 4))) {
                                        title = "今日督导时长（小时）：" + (todaySsv.getActualDuration() == null ? 0 : todaySsv.getActualDuration());
                                        cell.setCellValue(title);
                                        break;
                                    } else if (r == 4 || r == (4 + (y * 4))) {
                                        title = "今日督导完成比例（%）：" + (todaySsv.getCompletionRatio() == null ? 0 : todaySsv.getCompletionRatio());
                                        cell.setCellValue(title);
                                        break;
                                    }
                                }
                            } else if (i == 1) {
                                for (int y = 0; y < communitys.length; y++) {
                                    StatisticsSortingVo ssv = new StatisticsSortingVo();
                                    for (StatisticsSortingVo statisticsSortingVo : ssvList) {
                                        if (statisticsSortingVo.getCommunity().equals(communitys[y])) {
                                            ssv = statisticsSortingVo;
                                            break;
                                        }
                                    }
                                    List<StatisticsSortingVo> monthList = sev.getSortingMonthList();
                                    StatisticsSortingVo monthStv = new StatisticsSortingVo();
                                    for (StatisticsSortingVo statisticsSortingVo : monthList) {
                                        if (statisticsSortingVo.getCommunity().equals(communitys[y])) {
                                            monthStv = statisticsSortingVo;
                                            break;
                                        }
                                    }
                                    String title = "";
                                    if (r == 2 || r == (2 + (y * 4))) {
                                        title = "督导收集点数量：" + (ssv.getCollectionNum() == null ? 0 : ssv.getCollectionNum());
                                        cell.setCellValue(title);
                                        break;
                                    } else if (r == 3 || r == (3 + (y * 4))) {
                                        title = "本月累计督导时长（小时）：" + (monthStv.getActualDuration() == null ? 0 : monthStv.getActualDuration());
                                        cell.setCellValue(title);
                                        break;
                                    } else if (r == 4 || r == (4 + (y * 4))) {
                                        title = "本月累计督导完成比例（%）：" + (monthStv.getCompletionRatio() == null ? 0 : monthStv.getCompletionRatio());
                                        cell.setCellValue(title);
                                        break;
                                    }
                                }
                            } else {
                                if (i == 3) {
                                    String title = "";
                                    if (r == svList.size() + 2) {
                                        title = "岗前培训图片";
                                    }
                                    cell.setCellValue(date != "" ? r + "" : title);
                                } else if (i == 4) {
                                    cell.setCellValue(date);
                                } else if (i == 5) {
                                    cell.setCellValue(community);
                                } else if (i == 6) {
                                    String title = "";
                                    if (r == svList.size() + 2) {
                                        title = "现场督导图片";
                                    }
                                    cell.setCellValue(collectionName != "" ? collectionName : title);
                                } else if (i == 7) {
                                    cell.setCellValue(deliveryName);
                                } else if (i == 8) {
                                    cell.setCellValue(superintendentUserName);
                                } else if (i == 9) {
                                    String title = "";
                                    if (r == svList.size() + 2) {
                                        title = "厨余垃圾情况图片";
                                    }
                                    cell.setCellValue(superintendentUserPhone != "" ? superintendentUserPhone : title);
                                } else if (i == 10) {
                                    cell.setCellValue(supervisionAsk);
                                } else if (i == 11) {
                                    cell.setCellValue(actualDuration);
                                }
                            }
                        } else if (r >= (4 * communitys.length) + 1) {
                            if (i == 0) {
                                if (r == 1 || r == (1 + (communitys.length * 4))) {
                                    cell.setCellValue("合计");
                                    sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 1));
                                } else if (r == 2 || r == (2 + (communitys.length * 4))) {
                                    cell.setCellValue("督导投放点数量：" + (sev.getTotalDeliveryNum() == null ? 0 : sev.getTotalDeliveryNum()));
                                } else if (r == 3 || r == (3 + (communitys.length * 4))) {
                                    cell.setCellValue("今日督导时长（小时）：" + (sev.getTotalTodayDuration() == null ? 0 : sev.getTotalTodayDuration()));
                                } else if (r == 4 || r == (4 + (communitys.length * 4))) {
                                    cell.setCellValue("今日督导完成比例（%）：" + (sev.getTotalTodayCompletionRatio() == null ? 0 : sev.getTotalTodayCompletionRatio()));
                                }
                            } else if (i == 1) {
                                if (r == 2 || r == (2 + (communitys.length * 4))) {
                                    cell.setCellValue("督导收集点数量：" + (sev.getTotalCollectionNum() == null ? 0 : sev.getTotalCollectionNum()));
                                } else if (r == 3 || r == (3 + (communitys.length * 4))) {
                                    cell.setCellValue("本月累计督导时长（小时）：" + (sev.getTotalThisMonthDuration() == null ? 0 : sev.getTotalThisMonthDuration()));
                                } else if (r == 4 || r == (4 + (communitys.length * 4))) {
                                    cell.setCellValue("本月累计督导完成比例（%）：" + (sev.getTotalThisMonthCompletionRatio() == null ? 0 : sev.getTotalThisMonthCompletionRatio()));
                                }
                            } else if (i == 2) {
                                cell.setCellValue("");
                            } else if (i == 3) {
                                cell.setCellValue(date != "" ? r + "" : "");
                            } else if (i == 4) {
                                cell.setCellValue(date);
                            } else if (i == 5) {
                                cell.setCellValue(community);
                            } else if (i == 6) {
                                cell.setCellValue(collectionName);
                            } else if (i == 7) {
                                cell.setCellValue(deliveryName);
                            } else if (i == 8) {
                                cell.setCellValue(superintendentUserName);
                            } else if (i == 9) {
                                cell.setCellValue(superintendentUserPhone);
                            } else if (i == 10) {
                                cell.setCellValue(supervisionAsk);
                            } else if (i == 11) {
                                cell.setCellValue(actualDuration);
                            }
                        }
                    }
                }

                //合并单元格
                if (svList.size() > 0) {
                    sheet.addMergedRegion(new CellRangeAddress(svList.size() + 2, svList.size() + 2, 3, 4));
                    sheet.addMergedRegion(new CellRangeAddress(svList.size() + 2, svList.size() + 2, 6, 7));
                    sheet.addMergedRegion(new CellRangeAddress(svList.size() + 2, svList.size() + 2, 9, 10));
                }

                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                int rowNum = svList.size() + 3;
                for (int i = 0; i < picList1.size(); i++) {
                    URL url = new URL(picList1.get(i));
                    HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                    conn.setRequestMethod("GET");
                    InputStream inStream = conn.getInputStream();
                    byte[] byteData = readInputStream(inStream);
                    // 图片一导出到单元格
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 3, rowNum, (short) 3, rowNum);
                    //插入图片
                    patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));

                    if (picList1.size() >= i + 2) {
                        url = new URL(picList1.get(i + 1));
                        conn = (HttpURLConnection) url.openConnection();
                        conn.setRequestMethod("GET");
                        inStream = conn.getInputStream();
                        byteData = readInputStream(inStream);
                        // 图片一导出到单元格
                        anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 4, rowNum, (short) 4, rowNum);
                        //插入图片
                        patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                    i++;
                    rowNum++;
                }

                rowNum = svList.size() + 3;
                for (int i = 0; i < picList2.size(); i++) {
                    URL url = new URL(picList2.get(i));
                    HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                    conn.setRequestMethod("GET");
                    InputStream inStream = conn.getInputStream();
                    byte[] byteData = readInputStream(inStream);

                    // 图片一导出到单元格
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, rowNum, (short) 6, rowNum);
                    //插入图片
                    patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));

                    if (picList2.size() >= i + 2) {
                        url = new URL(picList2.get(i + 1));
                        conn = (HttpURLConnection) url.openConnection();
                        conn.setRequestMethod("GET");
                        inStream = conn.getInputStream();
                        byteData = readInputStream(inStream);

                        // 图片一导出到单元格
                        anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 7, rowNum, (short) 7, rowNum);
                        //插入图片
                        patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                    i++;
                    rowNum++;
                }

                rowNum = svList.size() + 3;
                for (int i = 0; i < picList3.size(); i++) {
                    URL url = new URL(picList3.get(i));
                    HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                    conn.setRequestMethod("GET");
                    InputStream inStream = conn.getInputStream();
                    byte[] byteData = readInputStream(inStream);

                    // 图片一导出到单元格
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 9, rowNum, (short) 9, rowNum);
                    //插入图片
                    patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));

                    if (picList3.size() >= i + 2) {
                        url = new URL(picList3.get(i + 1));
                        conn = (HttpURLConnection) url.openConnection();
                        conn.setRequestMethod("GET");
                        inStream = conn.getInputStream();
                        byteData = readInputStream(inStream);

                        // 图片一导出到单元格
                        anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 10, rowNum, (short) 10, rowNum);
                        //插入图片
                        patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                    i++;
                    rowNum++;
                }
            }

            output.flush();
            workbook.write(output);
            output.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    private static byte[] readInputStream(InputStream inStream) throws Exception {
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        //创建一个Buffer字符串
        byte[] buffer = new byte[1024];
        //每次读取的字符串长度，如果为-1，代表全部读取完毕
        int len = 0;
        //使用一个输入流从buffer里把数据读取出来
        while ((len = inStream.read(buffer)) != -1) {
            //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
            outStream.write(buffer, 0, len);
        }
        //关闭输入流
        inStream.close();
        //把outStream里的数据写入内存
        return outStream.toByteArray();
    }
}
